﻿
CREATE PROCEDURE [dbo].[FC_Productos_Search_DeForecast]     
@IdUsuario int,    
@IdEntityF int,  
@IdCustomer int,
@IdUOM int,
@IdProduct int,
@IdSegmento INT,
@IdProductLine INT,
@CodCustomer varchar(15) = NULL 
as    
begin    
 SELECT PR.IdProduct, PR.IdSegmentPL, C.IdCustomer, C.Codigo as CodCliente, C.Name as NameCustomer, SEG.DesSegment, PL.DesProductLine, PR.DesProduct, UO.CodUOM, UO.DesUOM     
 FROM FC_Forecast F     
   LEFT JOIN FC_Period P ON F.IdPeriod = P.IdPeriod     
   LEFT JOIN FC_Customer C ON P.IdEntityF = C.IdEntityF  AND F.IdCustomer = C.IdCustomer     
   LEFT JOIN FC_Product PR ON F.IdProduct = PR.IdProduct     
   LEFT JOIN FC_UOM UO ON PR.IdUOM = UO.IdUOM    
   LEFT JOIN FC_SegmentPL SE ON SE.IdSegmentPL = PR.IdSegmentPL
   LEFT JOIN FC_Segment SEG ON SE.IdSegment = SEG.IdSegment
   LEFT JOIN FC_ProductLine PL on SE.IdProductLine = PL.IdProductLine      
 WHERE     
 F.IdUsuario = @IdUsuario AND PR.IdEntityF = @IdEntityF  
 AND ((@IdCustomer = 0) or (F.IdCustomer = @IdCustomer))
 AND ((@IdUOM = 0) or (PR.IdUOM = @IdUOM))
 AND ((@IdProduct = 0) or (F.IdProduct = @IdProduct))
 AND ((@IdSegmento = 0) or (SE.IdSegment = @IdSegmento))
 AND ((@IdProductLine = 0) or (SE.IdProductLine = @IdProductLine))
 AND ((@CodCustomer = '') OR (C.Codigo  LIKE '%'+@CodCustomer+'%'))
 GROUP BY PR.IdProduct, PR.IdSegmentPL, C.IdCustomer, C.Codigo, C.Name, SEG.DesSegment, PL.DesProductLine, PR.DesProduct, UO.CodUOM, UO.DesUOM   
 ORDER BY C.Codigo, PL.DesProductLine, PR.DesProduct
    /*     
    (P.DateClose > DATEADD(MONTH, -10, GETDATE()) AND P.DateClose < DATEADD(MONTH, 14, GETDATE()))     
    AND C.IdEntityF = 2    
    */    
end